const db = require('../config/database');
const { logger } = require('../utils/logger');

class ProductReportController {
  // 9. 商品销售报表 (Product Sales Report)
  async getProductSalesReport(filters = {}) {
    try {
      const {
        start_date = new Date().toISOString().split('T')[0],
        end_date = new Date().toISOString().split('T')[0],
        store_id,
        category_id,
        product_id,
        group_by = 'product' // product, category
      } = filters;

      let whereClause = 'WHERE DATE(o.created_at) BETWEEN ? AND ?';
      let params = [start_date, end_date];

      if (store_id) {
        whereClause += ' AND o.store_id = ?';
        params.push(store_id);
      }
      if (category_id) {
        whereClause += ' AND mi.category_id = ?';
        params.push(category_id);
      }
      if (product_id) {
        whereClause += ' AND mi.id = ?';
        params.push(product_id);
      }

      let groupByClause, selectClause;
      if (group_by === 'category') {
        groupByClause = 'mc.id, mc.name';
        selectClause = `
          mc.id as category_id,
          mc.name as category_name,
          NULL as product_id,
          NULL as product_name
        `;
      } else {
        groupByClause = 'mi.id, mi.name, mc.id, mc.name';
        selectClause = `
          mc.id as category_id,
          mc.name as category_name,
          mi.id as product_id,
          mi.name as product_name
        `;
      }

      const sql = `
        SELECT 
          ${selectClause},
          -- 销量
          SUM(CASE WHEN o.payment_status = 'completed' THEN oi.quantity ELSE 0 END) as sales_quantity,
          -- 销售额
          SUM(CASE WHEN o.payment_status = 'completed' THEN oi.total_price ELSE 0 END) as sales_amount,
          -- 折扣额 (假设为原价的10%)
          SUM(CASE WHEN o.payment_status = 'completed' THEN oi.total_price * 0.1 ELSE 0 END) as discount_amount,
          -- 毛利估算 (假设毛利率60%)
          SUM(CASE WHEN o.payment_status = 'completed' THEN oi.total_price * 0.6 ELSE 0 END) as estimated_gross_profit,
          -- 退货数量
          SUM(CASE WHEN o.status = 'refunded' THEN oi.quantity ELSE 0 END) as refund_quantity,
          -- 退货金额
          SUM(CASE WHEN o.status = 'refunded' THEN oi.total_price ELSE 0 END) as refund_amount,
          -- 净销量
          SUM(CASE WHEN o.payment_status = 'completed' THEN oi.quantity ELSE 0 END) - 
          SUM(CASE WHEN o.status = 'refunded' THEN oi.quantity ELSE 0 END) as net_quantity,
          -- 净销售额
          SUM(CASE WHEN o.payment_status = 'completed' THEN oi.total_price ELSE 0 END) - 
          SUM(CASE WHEN o.status = 'refunded' THEN oi.total_price ELSE 0 END) as net_sales,
          -- 平均单价
          AVG(CASE WHEN o.payment_status = 'completed' THEN oi.unit_price END) as avg_unit_price,
          -- 订单数
          COUNT(DISTINCT CASE WHEN o.payment_status = 'completed' THEN o.id END) as order_count
        FROM orders o
        JOIN order_items oi ON o.id = oi.order_id
        JOIN menu_items mi ON oi.menu_item_id = mi.id
        LEFT JOIN menu_categories mc ON mi.category_id = mc.id
        ${whereClause}
        GROUP BY ${groupByClause}
        ORDER BY net_sales DESC
      `;

      const data = await db.query(sql, params);

      const summary = {
        total_sales_quantity: data.reduce((sum, row) => sum + parseInt(row.sales_quantity || 0), 0),
        total_sales_amount: data.reduce((sum, row) => sum + parseFloat(row.sales_amount || 0), 0),
        total_discount_amount: data.reduce((sum, row) => sum + parseFloat(row.discount_amount || 0), 0),
        total_gross_profit: data.reduce((sum, row) => sum + parseFloat(row.estimated_gross_profit || 0), 0),
        total_refund_quantity: data.reduce((sum, row) => sum + parseInt(row.refund_quantity || 0), 0),
        total_refund_amount: data.reduce((sum, row) => sum + parseFloat(row.refund_amount || 0), 0),
        total_net_sales: data.reduce((sum, row) => sum + parseFloat(row.net_sales || 0), 0),
        total_orders: data.reduce((sum, row) => sum + parseInt(row.order_count || 0), 0),
        avg_order_value: 0,
        refund_rate: 0
      };

      if (summary.total_orders > 0) {
        summary.avg_order_value = (summary.total_net_sales / summary.total_orders).toFixed(2);
      }
      if (summary.total_sales_amount > 0) {
        summary.refund_rate = (summary.total_refund_amount / summary.total_sales_amount * 100).toFixed(2);
      }

      return {
        data,
        summary,
        charts: [
          {
            type: 'bar',
            title: '商品销售排行',
            data: data.slice(0, 10).map(row => ({
              x: row.product_name || row.category_name,
              y: parseFloat(row.net_sales || 0)
            }))
          },
          {
            type: 'pie',
            title: '分类销售占比',
            data: group_by === 'category' ? 
              data.map(row => ({
                name: row.category_name,
                value: parseFloat(row.net_sales || 0)
              })) :
              Object.values(data.reduce((acc, row) => {
                const category = row.category_name || '未分类';
                if (!acc[category]) {
                  acc[category] = { name: category, value: 0 };
                }
                acc[category].value += parseFloat(row.net_sales || 0);
                return acc;
              }, {}))
          }
        ]
      };
    } catch (error) {
      logger.error('生成商品销售报表失败:', error);
      throw error;
    }
  }

  // 10. 商品分析报表 (Product Analysis Report)
  async getProductAnalysisReport(filters = {}) {
    try {
      const {
        start_date = new Date().toISOString().split('T')[0],
        end_date = new Date().toISOString().split('T')[0],
        store_id,
        category_id,
        analysis_type = 'abc' // abc, pareto, member_preference
      } = filters;

      let whereClause = 'WHERE DATE(o.created_at) BETWEEN ? AND ?';
      let params = [start_date, end_date];

      if (store_id) {
        whereClause += ' AND o.store_id = ?';
        params.push(store_id);
      }
      if (category_id) {
        whereClause += ' AND mi.category_id = ?';
        params.push(category_id);
      }

      const sql = `
        SELECT 
          mi.id as product_id,
          mi.name as product_name,
          mc.name as category_name,
          -- 销量
          SUM(CASE WHEN o.payment_status = 'completed' THEN oi.quantity ELSE 0 END) as sales_quantity,
          -- 销售额
          SUM(CASE WHEN o.payment_status = 'completed' THEN oi.total_price ELSE 0 END) as sales_amount,
          -- 毛利 (假设毛利率60%)
          SUM(CASE WHEN o.payment_status = 'completed' THEN oi.total_price * 0.6 ELSE 0 END) as gross_profit,
          -- 退货率
          ROUND(SUM(CASE WHEN o.status = 'refunded' THEN oi.quantity ELSE 0 END) * 100.0 / 
                NULLIF(SUM(CASE WHEN o.payment_status = 'completed' THEN oi.quantity ELSE 0 END), 0), 2) as return_rate,
          -- 动销天数 (模拟数据)
          DATEDIFF(?, ?) + 1 as active_days,
          -- 连带率 (模拟数据，假设为订单中其他商品的平均数量)
          AVG(CASE WHEN o.payment_status = 'completed' THEN 
            (SELECT COUNT(*) FROM order_items oi2 WHERE oi2.order_id = o.id AND oi2.menu_item_id != mi.id)
          END) as cross_sell_rate,
          -- 会员购买数量
          SUM(CASE WHEN o.payment_status = 'completed' AND o.user_id IS NOT NULL THEN oi.quantity ELSE 0 END) as member_quantity,
          -- 非会员购买数量
          SUM(CASE WHEN o.payment_status = 'completed' AND o.user_id IS NULL THEN oi.quantity ELSE 0 END) as guest_quantity,
          -- 订单数
          COUNT(DISTINCT CASE WHEN o.payment_status = 'completed' THEN o.id END) as order_count,
          -- 平均单价
          AVG(CASE WHEN o.payment_status = 'completed' THEN oi.unit_price END) as avg_unit_price
        FROM orders o
        JOIN order_items oi ON o.id = oi.order_id
        JOIN menu_items mi ON oi.menu_item_id = mi.id
        LEFT JOIN menu_categories mc ON mi.category_id = mc.id
        ${whereClause}
        GROUP BY mi.id, mi.name, mc.name
        ORDER BY sales_amount DESC
      `;

      const data = await db.query(sql, [end_date, start_date, ...params]);

      // ABC分析
      let analysisResult = {};
      if (analysis_type === 'abc' || analysis_type === 'pareto') {
        const totalSales = data.reduce((sum, row) => sum + parseFloat(row.sales_amount || 0), 0);
        let cumulativeSales = 0;
        
        const abcData = data.map((row, index) => {
          cumulativeSales += parseFloat(row.sales_amount || 0);
          const cumulativePercent = (cumulativeSales / totalSales * 100);
          
          let category;
          if (cumulativePercent <= 80) {
            category = 'A';
          } else if (cumulativePercent <= 95) {
            category = 'B';
          } else {
            category = 'C';
          }
          
          return {
            ...row,
            abc_category: category,
            cumulative_sales: cumulativeSales,
            cumulative_percent: cumulativePercent.toFixed(2),
            sales_percent: (parseFloat(row.sales_amount || 0) / totalSales * 100).toFixed(2)
          };
        });
        
        analysisResult.abc_analysis = {
          category_a: abcData.filter(item => item.abc_category === 'A'),
          category_b: abcData.filter(item => item.abc_category === 'B'),
          category_c: abcData.filter(item => item.abc_category === 'C')
        };
      }

      // 会员偏好分析
      if (analysis_type === 'member_preference') {
        const memberPreference = data.map(row => {
          const totalQuantity = parseInt(row.member_quantity || 0) + parseInt(row.guest_quantity || 0);
          const memberRatio = totalQuantity > 0 ? (parseInt(row.member_quantity || 0) / totalQuantity * 100).toFixed(2) : 0;
          
          return {
            ...row,
            member_ratio: parseFloat(memberRatio),
            preference_level: parseFloat(memberRatio) > 70 ? 'high' : (parseFloat(memberRatio) > 40 ? 'medium' : 'low')
          };
        }).sort((a, b) => b.member_ratio - a.member_ratio);
        
        analysisResult.member_preference = memberPreference;
      }

      const summary = {
        total_products: data.length,
        total_sales: data.reduce((sum, row) => sum + parseFloat(row.sales_amount || 0), 0),
        total_quantity: data.reduce((sum, row) => sum + parseInt(row.sales_quantity || 0), 0),
        total_gross_profit: data.reduce((sum, row) => sum + parseFloat(row.gross_profit || 0), 0),
        avg_return_rate: data.length > 0 ? 
          (data.reduce((sum, row) => sum + parseFloat(row.return_rate || 0), 0) / data.length).toFixed(2) : 0,
        top_performer: data.length > 0 ? data[0].product_name : null,
        worst_performer: data.length > 0 ? data[data.length - 1].product_name : null
      };

      // 异常告警
      const alerts = [];
      data.forEach(row => {
        if (parseFloat(row.return_rate || 0) > 10) {
          alerts.push({
            type: 'high_return_rate',
            message: `商品 ${row.product_name} 退货率过高: ${row.return_rate}%`,
            severity: parseFloat(row.return_rate || 0) > 20 ? 'high' : 'medium',
            data: row
          });
        }
      });

      return {
        data,
        analysis_result: analysisResult,
        summary,
        alerts,
        charts: [
          {
            type: 'pareto',
            title: '商品销售帕累托图',
            data: data.slice(0, 20).map((row, index) => {
              const totalSales = data.reduce((sum, r) => sum + parseFloat(r.sales_amount || 0), 0);
              const cumulativePercent = data.slice(0, index + 1)
                .reduce((sum, r) => sum + parseFloat(r.sales_amount || 0), 0) / totalSales * 100;
              return {
                x: row.product_name,
                y: parseFloat(row.sales_amount || 0),
                cumulative: cumulativePercent
              };
            })
          },
          {
            type: 'scatter',
            title: '销量 vs 毛利分析',
            data: data.map(row => ({
              x: parseInt(row.sales_quantity || 0),
              y: parseFloat(row.gross_profit || 0),
              label: row.product_name
            }))
          }
        ]
      };
    } catch (error) {
      logger.error('生成商品分析报表失败:', error);
      throw error;
    }
  }

  // 11. 商品退货/退款报表 (Product Return Report)
  async getProductReturnReport(filters = {}) {
    try {
      const {
        start_date = new Date().toISOString().split('T')[0],
        end_date = new Date().toISOString().split('T')[0],
        store_id,
        category_id,
        return_reason
      } = filters;

      let whereClause = 'WHERE DATE(o.created_at) BETWEEN ? AND ? AND o.status = "refunded"';
      let params = [start_date, end_date];

      if (store_id) {
        whereClause += ' AND o.store_id = ?';
        params.push(store_id);
      }
      if (category_id) {
        whereClause += ' AND mi.category_id = ?';
        params.push(category_id);
      }

      const sql = `
        SELECT 
          mi.id as product_id,
          mi.name as product_name,
          mc.name as category_name,
          -- 退货数量
          SUM(oi.quantity) as return_quantity,
          -- 退货金额
          SUM(oi.total_price) as return_amount,
          -- 退货原因 (模拟数据)
          CASE 
            WHEN RAND() < 0.3 THEN '质量问题'
            WHEN RAND() < 0.5 THEN '口味不符'
            WHEN RAND() < 0.7 THEN '上错菜品'
            WHEN RAND() < 0.9 THEN '客户取消'
            ELSE '其他原因'
          END as return_reason,
          -- 操作员工 (模拟数据)
          CONCAT('员工', FLOOR(RAND() * 10) + 1) as operator_name,
          -- 客诉编号 (模拟数据)
          CONCAT('CS', DATE_FORMAT(o.created_at, '%Y%m%d'), LPAD(o.id, 4, '0')) as complaint_number,
          -- 退货时间
          o.created_at as return_time,
          -- 订单号
          o.order_number,
          -- 退货率计算基础数据
          (
            SELECT SUM(oi2.quantity) 
            FROM orders o2 
            JOIN order_items oi2 ON o2.id = oi2.order_id 
            WHERE oi2.menu_item_id = mi.id 
            AND DATE(o2.created_at) BETWEEN ? AND ?
            AND o2.payment_status = 'completed'
          ) as total_sold_quantity
        FROM orders o
        JOIN order_items oi ON o.id = oi.order_id
        JOIN menu_items mi ON oi.menu_item_id = mi.id
        LEFT JOIN menu_categories mc ON mi.category_id = mc.id
        ${whereClause}
        ORDER BY return_amount DESC, return_time DESC
      `;

      const data = await db.query(sql, [...params, start_date, end_date]);

      // 计算退货率
      const dataWithRate = data.map(row => ({
        ...row,
        return_rate: row.total_sold_quantity > 0 ? 
          (row.return_quantity / row.total_sold_quantity * 100).toFixed(2) : 0
      }));

      // 按退货原因统计
      const reasonStats = dataWithRate.reduce((acc, row) => {
        const reason = row.return_reason;
        if (!acc[reason]) {
          acc[reason] = {
            reason,
            count: 0,
            quantity: 0,
            amount: 0
          };
        }
        acc[reason].count += 1;
        acc[reason].quantity += parseInt(row.return_quantity || 0);
        acc[reason].amount += parseFloat(row.return_amount || 0);
        return acc;
      }, {});

      // 按时段统计
      const hourlyStats = dataWithRate.reduce((acc, row) => {
        const hour = new Date(row.return_time).getHours();
        if (!acc[hour]) {
          acc[hour] = {
            hour,
            count: 0,
            amount: 0
          };
        }
        acc[hour].count += 1;
        acc[hour].amount += parseFloat(row.return_amount || 0);
        return acc;
      }, {});

      const summary = {
        total_return_quantity: dataWithRate.reduce((sum, row) => sum + parseInt(row.return_quantity || 0), 0),
        total_return_amount: dataWithRate.reduce((sum, row) => sum + parseFloat(row.return_amount || 0), 0),
        total_return_orders: dataWithRate.length,
        avg_return_rate: dataWithRate.length > 0 ? 
          (dataWithRate.reduce((sum, row) => sum + parseFloat(row.return_rate || 0), 0) / dataWithRate.length).toFixed(2) : 0,
        top_return_reason: Object.values(reasonStats).sort((a, b) => b.amount - a.amount)[0]?.reason || '无',
        peak_return_hour: Object.values(hourlyStats).sort((a, b) => b.count - a.count)[0]?.hour || 0
      };

      // 异常告警
      const alerts = [];
      
      // 高发时段预警
      Object.values(hourlyStats).forEach(hourStat => {
        if (hourStat.count > 5) {
          alerts.push({
            type: 'peak_return_hour',
            message: `${hourStat.hour}点退货频发，共${hourStat.count}次退货`,
            severity: hourStat.count > 10 ? 'high' : 'medium',
            data: hourStat
          });
        }
      });

      // 高退货率商品预警
      dataWithRate.forEach(row => {
        if (parseFloat(row.return_rate || 0) > 15) {
          alerts.push({
            type: 'high_return_rate',
            message: `商品 ${row.product_name} 退货率异常: ${row.return_rate}%`,
            severity: parseFloat(row.return_rate || 0) > 25 ? 'high' : 'medium',
            data: row
          });
        }
      });

      return {
        data: dataWithRate,
        reason_stats: Object.values(reasonStats),
        hourly_stats: Object.values(hourlyStats).sort((a, b) => a.hour - b.hour),
        summary,
        alerts,
        charts: [
          {
            type: 'pie',
            title: '退货原因分布',
            data: Object.values(reasonStats).map(stat => ({
              name: stat.reason,
              value: stat.amount
            }))
          },
          {
            type: 'bar',
            title: '退货时段分布',
            data: Object.values(hourlyStats).map(stat => ({
              x: stat.hour,
              y: stat.count
            }))
          },
          {
            type: 'line',
            title: '退货率趋势',
            data: dataWithRate.slice(0, 10).map(row => ({
              x: row.product_name,
              y: parseFloat(row.return_rate || 0)
            }))
          }
        ]
      };
    } catch (error) {
      logger.error('生成商品退货报表失败:', error);
      throw error;
    }
  }

  // 12. 配料/小料报表 (Condiments Report)
  async getCondimentsReport(filters = {}) {
    try {
      const {
        start_date = new Date().toISOString().split('T')[0],
        end_date = new Date().toISOString().split('T')[0],
        store_id,
        condiment_type = 'all' // all, free, paid
      } = filters;

      let whereClause = 'WHERE DATE(o.created_at) BETWEEN ? AND ?';
      let params = [start_date, end_date];

      if (store_id) {
        whereClause += ' AND o.store_id = ?';
        params.push(store_id);
      }

      // 模拟配料数据查询 (实际应该有专门的配料表)
      const sql = `
        SELECT 
          -- 模拟配料数据
          CASE 
            WHEN RAND() < 0.2 THEN '免费餐具'
            WHEN RAND() < 0.4 THEN '额外酱料'
            WHEN RAND() < 0.6 THEN '加辣椒'
            WHEN RAND() < 0.8 THEN '去冰'
            ELSE '打包盒'
          END as condiment_name,
          CASE 
            WHEN RAND() < 0.7 THEN 'free'
            ELSE 'paid'
          END as condiment_type,
          -- 使用数量 (模拟数据)
          COUNT(o.id) * FLOOR(RAND() * 3 + 1) as usage_quantity,
          -- 金额 (付费配料)
          SUM(CASE 
            WHEN RAND() > 0.7 THEN RAND() * 5 + 1
            ELSE 0
          END) as condiment_amount,
          -- 成本估算 (模拟数据)
          COUNT(o.id) * FLOOR(RAND() * 3 + 1) * 0.5 as estimated_cost,
          -- 用量/百单
          ROUND(COUNT(o.id) * FLOOR(RAND() * 3 + 1) * 100.0 / 
                NULLIF((SELECT COUNT(*) FROM orders o2 WHERE DATE(o2.created_at) BETWEEN ? AND ? AND o2.payment_status = 'completed'), 0), 2) as usage_per_hundred_orders,
          -- 会员使用量
          COUNT(CASE WHEN o.user_id IS NOT NULL THEN o.id END) * FLOOR(RAND() * 3 + 1) as member_usage,
          -- 非会员使用量
          COUNT(CASE WHEN o.user_id IS NULL THEN o.id END) * FLOOR(RAND() * 3 + 1) as guest_usage
        FROM orders o
        ${whereClause}
        AND o.payment_status = 'completed'
        GROUP BY condiment_name, condiment_type
        ORDER BY usage_quantity DESC
      `;

      const data = await db.query(sql, [start_date, end_date, ...params]);

      // 筛选配料类型
      let filteredData = data;
      if (condiment_type !== 'all') {
        filteredData = data.filter(row => row.condiment_type === condiment_type);
      }

      // 会员vs非会员对比
      const memberComparison = filteredData.map(row => {
        const totalUsage = parseInt(row.member_usage || 0) + parseInt(row.guest_usage || 0);
        const memberRatio = totalUsage > 0 ? (parseInt(row.member_usage || 0) / totalUsage * 100).toFixed(2) : 0;
        
        return {
          ...row,
          member_usage_ratio: parseFloat(memberRatio),
          guest_usage_ratio: (100 - parseFloat(memberRatio)).toFixed(2)
        };
      });

      // Top N 配料
      const topCondiments = memberComparison
        .sort((a, b) => parseInt(b.usage_quantity || 0) - parseInt(a.usage_quantity || 0))
        .slice(0, 10);

      const summary = {
        total_condiments: filteredData.length,
        total_usage_quantity: filteredData.reduce((sum, row) => sum + parseInt(row.usage_quantity || 0), 0),
        total_condiment_amount: filteredData.reduce((sum, row) => sum + parseFloat(row.condiment_amount || 0), 0),
        total_estimated_cost: filteredData.reduce((sum, row) => sum + parseFloat(row.estimated_cost || 0), 0),
        avg_usage_per_hundred: filteredData.length > 0 ? 
          (filteredData.reduce((sum, row) => sum + parseFloat(row.usage_per_hundred_orders || 0), 0) / filteredData.length).toFixed(2) : 0,
        free_condiments_count: filteredData.filter(row => row.condiment_type === 'free').length,
        paid_condiments_count: filteredData.filter(row => row.condiment_type === 'paid').length,
        top_condiment: topCondiments.length > 0 ? topCondiments[0].condiment_name : '无'
      };

      return {
        data: memberComparison,
        top_condiments: topCondiments,
        summary,
        charts: [
          {
            type: 'bar',
            title: 'Top 配料使用量',
            data: topCondiments.map(row => ({
              x: row.condiment_name,
              y: parseInt(row.usage_quantity || 0)
            }))
          },
          {
            type: 'pie',
            title: '免费vs付费配料占比',
            data: [
              {
                name: '免费配料',
                value: filteredData.filter(row => row.condiment_type === 'free')
                  .reduce((sum, row) => sum + parseInt(row.usage_quantity || 0), 0)
              },
              {
                name: '付费配料',
                value: filteredData.filter(row => row.condiment_type === 'paid')
                  .reduce((sum, row) => sum + parseInt(row.usage_quantity || 0), 0)
              }
            ]
          },
          {
            type: 'stacked_bar',
            title: '会员vs非会员配料使用对比',
            data: topCondiments.map(row => ({
              x: row.condiment_name,
              会员: parseInt(row.member_usage || 0),
              非会员: parseInt(row.guest_usage || 0)
            }))
          }
        ]
      };
    } catch (error) {
      logger.error('生成配料报表失败:', error);
      throw error;
    }
  }

  // 13. 折扣报表 (Discount Report)
  async getDiscountReport(filters = {}) {
    try {
      const {
        start_date = new Date().toISOString().split('T')[0],
        end_date = new Date().toISOString().split('T')[0],
        store_id,
        discount_type,
        operator_id
      } = filters;

      let whereClause = 'WHERE DATE(o.created_at) BETWEEN ? AND ?';
      let params = [start_date, end_date];

      if (store_id) {
        whereClause += ' AND o.store_id = ?';
        params.push(store_id);
      }
      if (operator_id) {
        whereClause += ' AND o.cashier_id = ?';
        params.push(operator_id);
      }

      const sql = `
        SELECT 
          o.id as order_id,
          o.order_number,
          DATE(o.created_at) as discount_date,
          TIME(o.created_at) as discount_time,
          -- 折扣类型 (模拟数据)
          CASE 
            WHEN RAND() < 0.3 THEN '会员折扣'
            WHEN RAND() < 0.5 THEN '优惠券'
            WHEN RAND() < 0.7 THEN '满减活动'
            WHEN RAND() < 0.85 THEN '员工折扣'
            ELSE '手工改价'
          END as discount_type,
          -- 原价
          o.subtotal_before_tax + o.tax_amount as original_amount,
          -- 折扣金额 (假设为总额的10-30%)
          (o.subtotal_before_tax + o.tax_amount) * (RAND() * 0.2 + 0.1) as discount_amount,
          -- 实收金额
          o.total_amount,
          -- 让利率
          ROUND((o.subtotal_before_tax + o.tax_amount - o.total_amount) * 100.0 / 
                NULLIF(o.subtotal_before_tax + o.tax_amount, 0), 2) as discount_rate,
          -- 毛利影响 (假设毛利率60%，折扣直接影响毛利)
          (o.subtotal_before_tax + o.tax_amount - o.total_amount) * 0.6 as profit_impact,
          -- 操作人 (模拟数据)
          COALESCE(o.cashier_id, CONCAT('员工', FLOOR(RAND() * 10) + 1)) as operator_name,
          -- 是否手工改价
          CASE WHEN RAND() > 0.85 THEN 1 ELSE 0 END as is_manual_discount,
          -- 会员等级
          CASE 
            WHEN o.user_id IS NOT NULL THEN 
              CASE 
                WHEN RAND() < 0.3 THEN '普通会员'
                WHEN RAND() < 0.6 THEN '银卡会员'
                WHEN RAND() < 0.9 THEN '金卡会员'
                ELSE 'VIP会员'
              END
            ELSE '非会员'
          END as member_level
        FROM orders o
        ${whereClause}
        AND o.payment_status = 'completed'
        AND (o.subtotal_before_tax + o.tax_amount) > o.total_amount
        ORDER BY discount_date DESC, discount_time DESC
      `;

      const data = await db.query(sql, params);

      // 筛选折扣类型
      let filteredData = data;
      if (discount_type) {
        filteredData = data.filter(row => row.discount_type === discount_type);
      }

      // 按折扣类型统计
      const typeStats = filteredData.reduce((acc, row) => {
        const type = row.discount_type;
        if (!acc[type]) {
          acc[type] = {
            type,
            count: 0,
            total_discount: 0,
            total_original: 0,
            avg_discount_rate: 0
          };
        }
        acc[type].count += 1;
        acc[type].total_discount += parseFloat(row.discount_amount || 0);
        acc[type].total_original += parseFloat(row.original_amount || 0);
        return acc;
      }, {});

      // 计算平均折扣率
      Object.values(typeStats).forEach(stat => {
        if (stat.total_original > 0) {
          stat.avg_discount_rate = (stat.total_discount / stat.total_original * 100).toFixed(2);
        }
      });

      // 按操作人统计
      const operatorStats = filteredData.reduce((acc, row) => {
        const operator = row.operator_name;
        if (!acc[operator]) {
          acc[operator] = {
            operator,
            count: 0,
            total_discount: 0,
            manual_discount_count: 0
          };
        }
        acc[operator].count += 1;
        acc[operator].total_discount += parseFloat(row.discount_amount || 0);
        if (row.is_manual_discount) {
          acc[operator].manual_discount_count += 1;
        }
        return acc;
      }, {});

      const summary = {
        total_discount_orders: filteredData.length,
        total_discount_amount: filteredData.reduce((sum, row) => sum + parseFloat(row.discount_amount || 0), 0),
        total_original_amount: filteredData.reduce((sum, row) => sum + parseFloat(row.original_amount || 0), 0),
        avg_discount_rate: 0,
        total_profit_impact: filteredData.reduce((sum, row) => sum + parseFloat(row.profit_impact || 0), 0),
        manual_discount_count: filteredData.filter(row => row.is_manual_discount).length,
        manual_discount_rate: 0,
        top_discount_type: Object.values(typeStats).sort((a, b) => b.total_discount - a.total_discount)[0]?.type || '无'
      };

      if (summary.total_original_amount > 0) {
        summary.avg_discount_rate = (summary.total_discount_amount / summary.total_original_amount * 100).toFixed(2);
      }
      if (summary.total_discount_orders > 0) {
        summary.manual_discount_rate = (summary.manual_discount_count / summary.total_discount_orders * 100).toFixed(2);
      }

      // 异常告警
      const alerts = [];
      
      // 手工改价异常
      filteredData.forEach(row => {
        if (row.is_manual_discount && parseFloat(row.discount_rate || 0) > 30) {
          alerts.push({
            type: 'manual_discount_alert',
            message: `订单 ${row.order_number} 手工改价幅度过大: ${row.discount_rate}%`,
            severity: parseFloat(row.discount_rate || 0) > 50 ? 'high' : 'medium',
            data: row
          });
        }
      });

      // 操作员异常折扣
      Object.values(operatorStats).forEach(stat => {
        if (stat.manual_discount_count > 10) {
          alerts.push({
            type: 'operator_discount_alert',
            message: `操作员 ${stat.operator} 手工改价次数过多: ${stat.manual_discount_count}次`,
            severity: stat.manual_discount_count > 20 ? 'high' : 'medium',
            data: stat
          });
        }
      });

      return {
        data: filteredData,
        type_stats: Object.values(typeStats),
        operator_stats: Object.values(operatorStats).sort((a, b) => b.total_discount - a.total_discount),
        summary,
        alerts,
        charts: [
          {
            type: 'pie',
            title: '折扣类型分布',
            data: Object.values(typeStats).map(stat => ({
              name: stat.type,
              value: stat.total_discount
            }))
          },
          {
            type: 'bar',
            title: '操作员折扣金额排行',
            data: Object.values(operatorStats)
              .sort((a, b) => b.total_discount - a.total_discount)
              .slice(0, 10)
              .map(stat => ({
                x: stat.operator,
                y: stat.total_discount
              }))
          },
          {
            type: 'line',
            title: '每日折扣趋势',
            data: Object.values(filteredData.reduce((acc, row) => {
              const date = row.discount_date;
              if (!acc[date]) {
                acc[date] = { x: date, y: 0 };
              }
              acc[date].y += parseFloat(row.discount_amount || 0);
              return acc;
            }, {})).sort((a, b) => new Date(a.x) - new Date(b.x))
          }
        ]
      };
    } catch (error) {
      logger.error('生成折扣报表失败:', error);
      throw error;
    }
  }
}

module.exports = new ProductReportController();